//=====================================================================
//
//  File:      DataAccessLayer.cpp
//  Summary:   Source code file for a project in MirroringFailover sample.
//
// 
//  Date:      May 8, 2005     
//
//---------------------------------------------------------------------
//
//  This file is part of the Microsoft SQL Server Code Samples.
//  Copyright (C) Microsoft Corporation.  All rights reserved.
//
//This source code is intended only as a supplement to Microsoft
//Development Tools and/or on-line documentation.  See these other
//materials for detailed information regarding Microsoft code samples.
//
//THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
//ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO 
//THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
//PARTICULAR PURPOSE.
//
//===================================================================== 

#include "DataAccessLayer.h"
#include < stdio.h >
#include < stdlib.h >
#include < vcclr.h >

using namespace System::Runtime::InteropServices;
using namespace System::Threading;
using namespace DataAccessLayer;

DAL::DAL() {
   SQLRETURN r;
   SQLHANDLE phenv, phdbc;

   // Allocate handle to local variable and then copy to instance variable
   // becasue we can't get an address for a managed instance variable 
   r = SQLAllocHandle(SQL_HANDLE_ENV, NULL, &phenv);
   henv = phenv;

   // This is an ODBC v3 application
   r = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, 0);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, NULL, NULL, true);
      exit(-1);
   }

   // Choose one of the following to test with or without connection pooling
   // r = SQLSetEnvAttr(henv, SQL_ATTR_CONNECTION_POOLING, (SQLPOINTER) SQL_CP_ONE_PER_DRIVER, 0);
   r = SQLSetEnvAttr(henv, SQL_ATTR_CONNECTION_POOLING, (SQLPOINTER) SQL_CP_OFF, 0);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, NULL, NULL, true);
      exit(-1);
   }

   // Allocate handle to local variable and then copy to instance variable
   // becasue we can't get an address for a managed instance variable 
   r = SQLAllocHandle(SQL_HANDLE_DBC, henv, &phdbc);
   hdbc = phdbc;

   // Run in ANSI/implicit transaction mode
   r = SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER) SQL_AUTOCOMMIT_OFF, SQL_IS_INTEGER);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, NULL, NULL, true);
      exit(-1);
   }

   Count = SavedCount = 0;
}

[System::Security::Permissions::SecurityPermissionAttribute(System::Security::Permissions::SecurityAction::Demand)]
void DAL::ODBCError(SQLHANDLE henv, SQLHANDLE hdbc, SQLHANDLE hstmt, bool ShowError) {
   SQLRETURN r;
   SQLCHAR szSqlState[6];
   SQLINTEGER fNativeError;
   SQLCHAR szErrorMsg[256];
   SQLSMALLINT cbErrorMsgMax = sizeof(szErrorMsg) - 1;
   SQLSMALLINT cbErrorMsg;
   CHAR text[1024], title[256];

   r = SQLError(henv, hdbc, hstmt, szSqlState, &fNativeError, szErrorMsg, cbErrorMsgMax, &cbErrorMsg);

   if (ShowError) {
      _snprintf_s(title, sizeof(title), _TRUNCATE, "ODBC Error %i", fNativeError, szErrorMsg);
      _snprintf_s(text, sizeof(text), _TRUNCATE, "[%s] - %s", szSqlState, szErrorMsg);

      MessageBox(NULL, (LPCTSTR) text, (LPCTSTR) "ODBC Error", MB_OK);
   }

   lastErrorMessage = Marshal::PtrToStringAnsi(static_cast<IntPtr>(szErrorMsg));
   lastSqlState = Marshal::PtrToStringAnsi(static_cast<IntPtr>(szSqlState));
   lastNativeError = fNativeError;
}

int DAL::Connect(String ^Primary, String ^Mirror, bool TrackChanges, bool showError) {
   SQLRETURN r;

   pin_ptr<const wchar_t> primary = PtrToStringChars(Primary);;
   pin_ptr<const wchar_t> mirror = PtrToStringChars(Mirror);;
   SQLCHAR szConnStrIn[1024];

   // Remember to escape use input in the connection string in braces
   _snprintf_s((char *)szConnStrIn, 
      sizeof(szConnStrIn), 
      _TRUNCATE, 
      "Driver={SQL Server Native Client 10.0};Server={%S};Failover_Partner={%S};Database=HADB;Trusted_Connection=Yes", 
      primary, 
      mirror);

   r = SQLDriverConnect(hdbc, 
      NULL, 
      (SQLCHAR *) szConnStrIn, 
      SQL_NTS, 
      NULL, 
      0, 
      NULL, 
      SQL_DRIVER_NOPROMPT);

   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, NULL, showError);
      return -1;
   }

   SQLCHAR szAttrValue[128];
   SQLLEN cbColValue;
   SQLINTEGER cbAttrValue;

   SQLHANDLE hstmt;
   r = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);

   // Discover actual principal
   r = SQLExecDirect(hstmt, (SQLCHAR *) "select @@SERVERNAME", SQL_NTS);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, showError);
      return 0;
   }
   
   r = SQLFetch(hstmt);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, showError);
      return 0;
   }

   r = SQLGetData(hstmt, 1, SQL_C_CHAR, szAttrValue, sizeof(szAttrValue), &cbColValue);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, showError);
      return 0;
   }
   r = SQLCloseCursor(hstmt);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, showError);
      return 0;
   }
   ActualPrincipal = gcnew String(reinterpret_cast<char *>(szAttrValue));

   // We have two choices here:
   // 1 - return the ODBC property, which isn't accure if we've failed over
   // 2 - query actual primary for its partner, which is always accurate
   if (ActualPrincipal != Primary && TrackChanges) {
      r = SQLExecDirect(hstmt, (SQLCHAR *) "select mirroring_partner_instance from sys.database_mirroring sdm join sys.databases sdb on sdb.database_id=sdm.database_id where sdb.name='HADB'", SQL_NTS);
      if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
         ODBCError(henv, hdbc, hstmt, showError);
         return 0;
      }

      r = SQLFetch(hstmt);
      if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
         ODBCError(henv, hdbc, hstmt, showError);
         return 0;
      }

      r = SQLGetData(hstmt, 1, SQL_C_CHAR, szAttrValue, sizeof(szAttrValue), &cbColValue);
      if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
         ODBCError(henv, hdbc, hstmt, showError);
         return 0;
      }

      r = SQLCloseCursor(hstmt);
      if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
         ODBCError(henv, hdbc, hstmt, showError);
         return 0;
      }
      ActualPartner = gcnew String(reinterpret_cast<char *>(szAttrValue));

   }
   else {
      r = SQLGetConnectAttr(hdbc, SQL_COPT_SS_FAILOVER_PARTNER, szAttrValue, sizeof(szAttrValue), &cbAttrValue);
      ActualPartner = gcnew String(reinterpret_cast<char *>(szAttrValue));
   }

   r = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO)
      ODBCError(henv, hdbc, hstmt, showError);

   // Leave connection in neutral transaction state
   r = SQLTransact(henv, hdbc, SQL_ROLLBACK);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO)
      ODBCError(henv, hdbc, NULL, showError);

   return 0;
}

int DAL::Disconnect(bool showError) {
   SQLRETURN r;
   bool detectedFailure = false;

   // Rollback both local and server state to retry the transaction when reconnected
   // This amounts to rolling back in-memory transaction state
   Count = SavedCount;

   // Rollback any uncommitted work before commit
   r = SQLTransact(henv, hdbc, SQL_ROLLBACK);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, NULL, showError);
      detectedFailure = true;
   }

   // ODBC driver will clean up the connection even if no network connection
   r = SQLDisconnect(hdbc);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      // If we captured an error on rollback, retain it 
      // otherwise capture the disconnect error
      if (!detectedFailure) {
         ODBCError(henv, hdbc, NULL, showError);
         detectedFailure = true;
      }
      // This will force the driver to do a client-side cleanup if it hasn't already
      SQLDisconnect(hdbc);
   }

   if (detectedFailure)
      return -1;
   else
      return 0;
}

int DAL::Update(bool ShowError) {
   SQLRETURN r;
   SQLHANDLE hstmt;
   int columnValue;

   // Do some work in the database ...
   r = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, ShowError);
      return -1;
   }

   r = SQLExecDirect(hstmt, (SQLCHAR *) "select counter from MirrorTest", SQL_NTS);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, ShowError);
      return -1;
   }

   r = SQLFetch(hstmt);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, ShowError);
      return -1;
   }

   r = SQLGetData(hstmt, 1, SQL_C_LONG, &columnValue, sizeof(columnValue), NULL);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, ShowError);
      return -1;
   }

   r = SQLCloseCursor(hstmt);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, ShowError);
      return -1;
   }

   if (columnValue != Count) {
#if 0
      char msg[256];
      _snprintf_s(msg, sizeof(msg), _TRUNCATE, 
         "Logic Error! Count = %i, Column = %i, Saved = %i", 
         Count, columnValue, SavedCount);
      MessageBox(NULL,msg, "MirroringFailover Explorer", MB_OK);
#endif
      // Recover from logic error and continue the demo
      Count = SavedCount = columnValue;
      return -99;
   }

   Count++;
   r = SQLExecDirect(hstmt, (SQLCHAR *) "update Mirrortest set counter = counter + 1", SQL_NTS);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, ShowError);
      return -1;
   }

   r = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, ShowError);
      return -1;
   }

   return 0;
}

int DAL::Commit(bool ShowError) {
   SQLRETURN r;

   r = SQLTransact(henv, hdbc, SQL_COMMIT);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, NULL, ShowError);
      return -1;
   }

   // Commit local state once the server is committed
   SavedCount = Count;

   return 0;
}

int DAL::DoSomeWork() {
   int r = Update(false);

   // Adjust the delays here to give the server a chance to keep up
   // when running everything on the same machines
   Thread::Sleep(100); 

   if (r == 0)
      r = Commit(false);

   Thread::Sleep(100); 
   return r;
}

int DAL::MirroringFailover() {
   SQLRETURN r;
   SQLHANDLE hstmt;

   // Force manual failover to partner on 
   r = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
   r = SQLExecDirect(hstmt, (SQLCHAR *) "use master", SQL_NTS);

   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO)
      ODBCError(henv, hdbc, hstmt, true);

   if (r == SQL_SUCCESS || r == SQL_SUCCESS_WITH_INFO) {
      r = SQLTransact(henv, hdbc, SQL_ROLLBACK);
      if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO)
         ODBCError(henv, hdbc, hstmt, true);
   }

   if (r == SQL_SUCCESS || r == SQL_SUCCESS_WITH_INFO) {
      r = SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER) SQL_AUTOCOMMIT_ON, SQL_IS_INTEGER);
      if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO)
         ODBCError(henv, hdbc, hstmt, true);
   }

   if (r == SQL_SUCCESS || r == SQL_SUCCESS_WITH_INFO) {
      r = SQLExecDirect(hstmt, (SQLCHAR *) "alter database HADB set partner failover", SQL_NTS);

      if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO)
         ODBCError(henv, hdbc, hstmt, true);
   }
   return 0;
}

int DAL::InitTable() {
   SQLRETURN r;
   SQLHANDLE hstmt;

   // Setup for continuous tes
   r = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
   r = SQLExecDirect(hstmt, (SQLCHAR *) "drop table MirrorTest", SQL_NTS);
   r = SQLExecDirect(hstmt, (SQLCHAR *) "create table MirrorTest (counter int)", SQL_NTS);

   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, true);
      return -1;
   }

   r = SQLExecDirect(hstmt, (SQLCHAR *) "insert into MirrorTest values (1)", SQL_NTS);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, true);
      return -1;
   }

   r = SQLTransact(henv, hdbc, SQL_COMMIT);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, true);
      return -1;
   }

   r = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, true);
      return 0;
   }

   // Initialize local transaction state
   Count = SavedCount = 1;

   return 0;
}